﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.InteropServices;
using BrightSword.OfficeInterop.Internal;
using BrightSword.SwissKnife;
using BrightSword.Feber.Core;
using Microsoft.CSharp.RuntimeBinder;
using Microsoft.Office.Interop.Excel;
using Binder = Microsoft.CSharp.RuntimeBinder.Binder;

namespace BrightSword.OfficeInterop
{
    /// <summary>
    /// This class provides a mechanism to read each row of an Excel worksheet into a strongly-typed collection
    /// </summary>
    /// <example>
    /// The following code opens a specified Excel workbook maps each row into a Template object:
    /// 
    /// <code>
    ///            foreach (var item in FastExcelObjectReader.ReadItems&lt;Template&gt;(_params.Input))
    ///            {
    ///                item.Print();
    ///            }
    ///</code>
    /// </example>
    /// <remarks>
    /// This code is made available for open-source use at http://excelreader.codeplex.com/
    /// </remarks>
    /// <see cref="http://excelreader.codeplex.com/"/>
    public static class FastExcelObjectReader
    {
        /// <summary>
        /// Method to read rows in the specified Excel workbook into instances of <typeparamref name="TTemplate"/>
        /// </summary>
        /// <typeparam name="TTemplate">Instantiable type specifying the template to map the row into</typeparam>
        /// <param name="filename">Required parameter specifying the filename to read and process</param>
        /// <param name="sheetName">Optional parameter specifying the sheet name within the file to process. If none is provided, all sheets are processed</param>
        /// <param name="headerRowIndex">Optional parameter specifying the row to use as the Header row. The values in the Header are correlated to the properties of <typeparamref name="TTemplate"/> using the value specified in the <see cref="ColumnHeaderAttribute"/> attribute decorating the property</param>
        /// <returns>An enumeration of <typeparamref name="TTemplate"/> objects corresponding to the rows in <paramref name="filename"/> </returns>
        public static IEnumerable<ContextfulMappedObject<TTemplate>> ReadItems<TTemplate>(string filename, string sheetName = null, int headerRowIndex = 1, int maxColumnCount = int.MaxValue)
            where TTemplate : new()
        {
            using (var app = new ExcelApplicationWrapper(filename))
            {
                var worksheets = app.Workbook.Sheets;

                try
                {
                    var sheets = String.IsNullOrEmpty(sheetName)
                        ? worksheets.Cast<Worksheet>()
                        : worksheets.Cast<Worksheet>().Where(_sheet => String.Compare(_sheet.Name, sheetName, true) == 0);

                    foreach (var worksheet in sheets)
                    {
                        var mappingLambda = TemplateFeberMapper<TTemplate>.BuildMappingLambda(worksheet);

                        try
                        {
                            var rows = worksheet.Rows;

                            try
                            {
                                for (var rowIndex = 1; rowIndex <= rows.Count; rowIndex++)
                                {
                                    if (rowIndex == headerRowIndex)
                                    {
                                        continue;
                                    }

                                    if (worksheet.IsBlank(rowIndex, maxColumnCount))
                                    {
                                        break;
                                    }

                                    yield return new ContextfulMappedObject<TTemplate>
                                    {
                                        WorksheetName = worksheet.Name,
                                        RowIndex = rowIndex,
                                        MappedObject = mappingLambda(worksheet, rowIndex)
                                    };
                                }
                            }
                            finally
                            {
                                Marshal.ReleaseComObject(rows);
                            }
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(worksheet);
                        }
                    }

                    yield break;    
                }
                finally
                {
                    Marshal.ReleaseComObject(worksheets);
                }
            }
        }

        #region Nested type: TemplateFeberMapper

        internal static class TemplateFeberMapper<TTemplate>
            where TTemplate : new()
        {
            private static readonly MethodInfo _consoleWriteLine = typeof (Console).GetMethod(
                "WriteLine", BindingFlags.Static | BindingFlags.Public, null, new [ ] { typeof (string), typeof (object [ ]) }, null);

            public static Func<Worksheet, int, TTemplate> BuildMappingLambda(Worksheet worksheet, int headerRowIndex = 1)
            {
                // create the [_worksheet] parameter
                var worksheetParameterExpression = Expression.Parameter(typeof (Worksheet), "_worksheet");

                // create the [_rowIndex] parameter
                var rowIndexExpression = Expression.Parameter(typeof (int), "_rowIndex");

                // TTemplate result = new TTemplate()
                var resultExpression = Expression.Variable(typeof (TTemplate), "result");
                var newResultExpression = Expression.Assign(resultExpression, Expression.New(typeof (TTemplate)));

                // var rows = worksheet.Rows;
                var rowsExpression = Expression.Variable(typeof (Range), "rows");
                var rowsAssignmentExpression = Expression.Assign(
                    rowsExpression, worksheetParameterExpression.GetDynamicPropertyAccessorExpression<Worksheet>("Rows", typeof (Range)));

                var columnMapExpressions = BuildColumnMapExpressions(worksheet, resultExpression, rowsExpression, rowIndexExpression);
                var rowsReleaseExpression = Expression.TryFinally(
                    Expression.Block(columnMapExpressions), Expression.Call(typeof (Marshal), "ReleaseComObject", null, rowsExpression));

                var blockExpression = Expression.Block(
                    new [ ] { resultExpression, rowsExpression },
                    newResultExpression,
                    rowsAssignmentExpression,
                    Expression.IfThen(Expression.NotEqual(Expression.Constant(null), rowsExpression), rowsReleaseExpression),
                    resultExpression);

                var lambdaExpression = Expression.Lambda<Func<Worksheet, int, TTemplate>>(
                    blockExpression, worksheetParameterExpression, rowIndexExpression);

                return lambdaExpression.Compile();
            }

            public static IEnumerable<Expression> BuildColumnMapExpressions(
                Worksheet worksheet,
                ParameterExpression resultExpression,
                ParameterExpression rowsExpression,
                ParameterExpression rowIndexExpression,
                int headerRowIndex = 1)
            {
                var binderRangeCellsObjectObjectObject = Binder.InvokeMember(
                    CSharpBinderFlags.InvokeSimpleName,
                    "Cells",
                    new [ ] { typeof (object), typeof (object) },
                    typeof (Range),
                    new [ ]
                    {
                        CSharpArgumentInfo.Create(CSharpArgumentInfoFlags.None, "_this"),
                        CSharpArgumentInfo.Create(CSharpArgumentInfoFlags.Constant | CSharpArgumentInfoFlags.UseCompileTimeType, "row"),
                        CSharpArgumentInfo.Create(CSharpArgumentInfoFlags.Constant | CSharpArgumentInfoFlags.UseCompileTimeType, "column"),
                    });

                var rows = worksheet.Rows;
                try
                {
                    var headerRow = rows[headerRowIndex];
                    try
                    {
                        var columns = rows.Columns;
                        try
                        {
                            var attributedProperties = from _pi in typeof (TTemplate).GetProperties()
                                                       let __attr = _pi.GetCustomAttribute<ColumnHeaderAttribute>()
                                                       select new {
                                                           ColumnHeader = __attr.ColumnHeaderText,
                                                           PropertyInfo = _pi
                                                       };
                            foreach (var attributedProperty in attributedProperties)
                            {
                                for (var columnIndex = 1; columnIndex <= columns.Count; columnIndex++)
                                {
                                    var cell = rows.Cells[headerRowIndex, columnIndex] as Range;
                                    Debug.Assert(cell != null, "Why is this not a range?");

                                    try
                                    {
                                        var headerText = cell.Text as string;
                                        if (String.IsNullOrWhiteSpace(headerText))
                                        {
                                            continue;
                                        }

                                        if (String.Compare(headerText, attributedProperty.ColumnHeader, true) != 0)
                                        {
                                            continue;
                                        }

                                        var propertyInfo = attributedProperty.PropertyInfo;

                                        var cellExpression = Expression.Variable(typeof (Range), "cell");

                                        // var cell = (Range)rows.Cells[rowIndex, columnIndex];
                                        var cellAssignmentExpression = Expression.Assign(
                                            cellExpression,
                                            Expression.Convert(
                                                Expression.Dynamic(
                                                    binderRangeCellsObjectObjectObject,
                                                    typeof (object),
                                                    rowsExpression,
                                                    rowIndexExpression,
                                                    Expression.Constant(columnIndex, typeof (object))),
                                                typeof (Range)));

                                        // var rawValue = cell.Text;
                                        var rawValueExpression = Expression.Property(cellExpression, "Text");

                                        // trimmedValue = rawValue == null ? rawValue : rawValue.ToString().Trim();
                                        var trimmedValueExpression =
                                            Expression.Condition(
                                                Expression.Equal(Expression.Constant(null), rawValueExpression),
                                                Expression.Constant(String.Empty),
                                                Expression.Call(Expression.Call(rawValueExpression, "ToString", null), "Trim", null));

                                        // var coercedValue = CoerceExtensions.CoerceType(trimmedValue, propertyType, <default>);
                                        var coercedValueExpression = Expression.Call(
                                            typeof (CoerceExtensions),
                                            "CoerceType",
                                            null,
                                            trimmedValueExpression,
                                            Expression.Constant(propertyInfo.PropertyType),
                                            Expression.Constant(Expression.Default(propertyInfo.PropertyType)));

                                        // result.<property> = <type>coercedValue;
                                        var propertyAssignmentExpression = Expression.Assign(
                                            Expression.Property(resultExpression, propertyInfo),
                                            Expression.Convert(coercedValueExpression, propertyInfo.PropertyType));

                                        // try
                                        // {
                                        //    ...assignment goes here...  
                                        // }
                                        // finally
                                        // {
                                        //    Marshal.ReleaseComObject(cell);
                                        // }
                                        var cellReleaseExpression = Expression.TryFinally(
                                            Expression.Block(propertyAssignmentExpression),
                                            Expression.Call(typeof (Marshal), "ReleaseComObject", null, cellExpression));

                                        var exceptionVariable = Expression.Variable(typeof (Exception), "_exception");

                                        var blockExpression =
                                            Expression.TryCatch(
                                                Expression.Block(cellAssignmentExpression, cellReleaseExpression, Expression.Default(typeof (void))),
                                                Expression.Catch(
                                                    exceptionVariable,
                                                    Expression.Block(
                                                        Expression.Call(
                                                            _consoleWriteLine,
                                                            Expression.Constant("Exception {0} occurred for row {1}, column {2}", typeof (string)),
                                                            Expression.NewArrayInit(
                                                                typeof (object),
                                                                Expression.Property(exceptionVariable, "Message"),
                                                                Expression.Convert(rowIndexExpression, typeof (object)),
                                                                Expression.Constant(columnIndex, typeof (object)))),
                                                        Expression.Rethrow())));

                                        yield return Expression.Block(new [ ] { cellExpression, exceptionVariable }, blockExpression);
                                        break;
                                    }
                                    finally
                                    {
                                        Marshal.ReleaseComObject(cell);
                                    }
                                }
                            }

                            yield break; 
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(columns);
                        }
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(headerRow);
                    }
                }
                finally
                {
                    Marshal.ReleaseComObject(rows);
                }
            }
        }

        #endregion
    }
}